/*
 Navicat Premium Data Transfer

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 50512
 Source Host           : localhost
 Source Database       : lrdb

 Target Server Type    : MySQL
 Target Server Version : 50512
 File Encoding         : utf-8

 Date: 05/22/2011 03:19:59 AM
*/

SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
--  Table structure for `lr_request`
-- ----------------------------
DROP TABLE IF EXISTS `lr_request`;
CREATE TABLE `lr_request` (
  `REQ_ID` int(10) NOT NULL AUTO_INCREMENT,
  `UDID` varchar(40) CHARACTER SET latin1 NOT NULL,
  `ENRT_DATE` date NOT NULL,
  `REQ_CATEGORY` varchar(20) NOT NULL,
  `REQ_CONTENT` varchar(255) NOT NULL,
  `EXP_A` varchar(100) NOT NULL,
  `EXP_B` varchar(100) NOT NULL,
  `EXP_C` varchar(100) NOT NULL,
  `EXP_D` varchar(100) NOT NULL,
  `RES_COUNT` int(10) NOT NULL DEFAULT '0',
  PRIMARY KEY (`REQ_ID`),
  UNIQUE KEY `REQ_ID` (`REQ_ID`),
  UNIQUE KEY `LR_REQUEST_IDX` (`REQ_ID`,`UDID`,`ENRT_DATE`,`REQ_CONTENT`) USING BTREE,
  KEY `LR_USER_REQUEST_CONSTRAINT` (`UDID`),
  CONSTRAINT `LR_USER_REQUEST_CONSTRAINT` FOREIGN KEY (`UDID`) REFERENCES `LR_USER` (`UDID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
delimiter ;;
CREATE TRIGGER `TRI_LR_REQUEST_MANAGEBLOCK` BEFORE DELETE ON `lr_request` FOR EACH ROW BEGIN
	DECLARE l_blccount INT default 0;
	SELECT blc_count
	INTO l_blccount
	FROM lr_user WHERE udid = OLD.udid;
	
	IF (l_blccount < 3) THEN
		UPDATE lr_user SET blc_count = blc_count + 1 WHERE udid = OLD.udid;
	ELSE
		UPDATE lr_user SET aut_type = 'b' WHERE udid = OLD.udid;
	END IF;
	
END;
 ;;
delimiter ;

-- ----------------------------
--  Records of `lr_request`
-- ----------------------------
BEGIN;
INSERT INTO `lr_request` VALUES ('1', 'A15', '2011-05-20', '결혼', '집안에서 결혼에 대한 압박이 심할때 내 남자친구가 결혼자금이 너무 부족하다면 어떻게 하시겠습니까?', '성실하다면 참고 기다려보겠다.', '현실적으로 불가능하다고 판단하여 마음의 정리를 하겠다.', '조금 참으면서 준비를 하면서 부모님께 인사시켜드리고 부모님을 설득하겠다.', '잘모르겠다. 그냥 이대로 상황을 더 지켜보겠다.', '4'), ('2', 'A1', '2011-05-20', '바람', '내 남자친구가 바람을 피운 사실을 알게되었을때 어떻게 하시겠습니까?', '그 순간 정리한다.', '남자친구에게 직접 사실을 확인하고 정리한다.', '남자친구가 반성한다면 한번만 용서한다.', '한번은 모른척하고 눈감아준다.', '0'), ('3', 'A0', '2011-05-20', '기념일', '내 남자친구가 100일 잊어버리고 넘어갔습니다. 어떻게 하시겠습니까?', '관계를 심각하게 생각해본다.', '조금 서운하지만 별 문제는 되지 않는다.', '남자친구에게 사과를 받아낸다.', '다음 기념일을 지켜보겠다.', '0'), ('4', 'A16', '2011-05-20', '섹스', '내 여자친구가 혼전관계를 용납하지 않습니다. 어떻게 하시겠습니까?', '이해할수 없다. 관계를 정리하겠다.', '이해한다. 순결을 지켜주겠다.', '더 가까워지도록 노력해서 관계를 가지도록 하겠다.', '몰래 바람이라도 펴서 해결해 보겠다.', '0');
COMMIT;

-- ----------------------------
--  Table structure for `lr_response`
-- ----------------------------
DROP TABLE IF EXISTS `lr_response`;
CREATE TABLE `lr_response` (
  `REQ_ID` int(10) NOT NULL,
  `UDID` varchar(40) CHARACTER SET latin1 NOT NULL,
  `RESPONSE` char(1) NOT NULL,
  PRIMARY KEY (`REQ_ID`,`UDID`),
  UNIQUE KEY `REQ_ID` (`REQ_ID`,`UDID`),
  UNIQUE KEY `LR_RESPONSE_IDX` (`REQ_ID`,`UDID`) USING BTREE,
  KEY `LR_USER_RESPONSE_CONSTRAINT` (`UDID`),
  CONSTRAINT `LR_REQUEST_RESPONSE_CONSTRAINT` FOREIGN KEY (`REQ_ID`) REFERENCES `LR_REQUEST` (`REQ_ID`) ON DELETE CASCADE,
  CONSTRAINT `LR_USER_RESPONSE_CONSTRAINT` FOREIGN KEY (`UDID`) REFERENCES `LR_USER` (`UDID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
delimiter ;;
CREATE TRIGGER `TRI_LR_REQUEST_ADDCOUNT` AFTER INSERT ON `lr_response` FOR EACH ROW BEGIN
	UPDATE lr_request SET res_count = res_count + 1 WHERE req_id = NEW.req_id;
	
END;
 ;;
delimiter ;

-- ----------------------------
--  Records of `lr_response`
-- ----------------------------
BEGIN;
INSERT INTO `lr_response` VALUES ('1', 'A2', 'C'), ('1', 'A21', 'A');
COMMIT;

-- ----------------------------
--  Table structure for `lr_rstconfirm`
-- ----------------------------
DROP TABLE IF EXISTS `lr_rstconfirm`;
CREATE TABLE `lr_rstconfirm` (
  `REQ_ID` int(10) NOT NULL,
  `UDID` varchar(40) CHARACTER SET latin1 NOT NULL,
  `RST_CONFIRM` char(1) NOT NULL DEFAULT 'n' COMMENT '사용자의 질문에 대한 결과확인 여부',
  PRIMARY KEY (`REQ_ID`,`UDID`),
  UNIQUE KEY `REQ_ID` (`REQ_ID`,`UDID`),
  UNIQUE KEY `LR_RSTCONFIRM_IDX` (`REQ_ID`,`UDID`) USING BTREE,
  KEY `LR_USER_RSTCONFIRM_CONSTRAINT` (`UDID`),
  CONSTRAINT `LR_REQUEST_RSTCONFIRM_CONSTRAINT` FOREIGN KEY (`REQ_ID`) REFERENCES `LR_REQUEST` (`REQ_ID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `LR_USER_RSTCONFIRM_CONSTRAINT` FOREIGN KEY (`UDID`) REFERENCES `LR_USER` (`UDID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `lr_rstconfirm`
-- ----------------------------
BEGIN;
INSERT INTO `lr_rstconfirm` VALUES ('1', 'A20', 'y');
COMMIT;

-- ----------------------------
--  Table structure for `lr_user`
-- ----------------------------
DROP TABLE IF EXISTS `lr_user`;
CREATE TABLE `lr_user` (
  `UDID` varchar(40) CHARACTER SET latin1 NOT NULL,
  `AGE` char(2) CHARACTER SET latin1 NOT NULL,
  `SEX` char(1) CHARACTER SET latin1 NOT NULL,
  `BLOODTYPE` varchar(2) CHARACTER SET latin1 NOT NULL,
  `AUT_TYPE` char(1) CHARACTER SET latin1 NOT NULL,
  `RST_COUNT` varchar(20) CHARACTER SET latin1 NOT NULL COMMENT 'date + count 20110520_00000000000',
  `BLC_COUNT` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`UDID`),
  UNIQUE KEY `UDID` (`UDID`),
  UNIQUE KEY `LR_USER_IDX` (`UDID`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `lr_user`
-- ----------------------------
BEGIN;
INSERT INTO `lr_user` VALUES ('A0', '10', 'm', 'a', 'n', '2011-05-20_0', '0'), ('A1', '20', 'f', 'b', 'n', '2011-05-20_0', '0'), ('A10', '20', 'f', 'a', 'n', '2011-05-20_0', '0'), ('A11', '10', 'm', 'ab', 'n', '2011-05-20_0', '0'), ('A13', '20', 'm', 'b', 'n', '2011-05-20_0', '0'), ('A14', '10', 'f', 'o', 'n', '2011-05-20_0', '0'), ('A15', '30', 'm', 'a', 'n', '2011-05-20_0', '0'), ('A16', '20', 'f', 'b', 'n', '2011-05-20_0', '0'), ('A17', '10', 'm', 'b', 'n', '2011-05-20_0', '0'), ('A18', '20', 'f', 'ab', 'n', '2011-05-20_0', '0'), ('A19', '30', 'm', 'b', 'n', '2011-05-20_0', '0'), ('A2', '30', 'm', 'o', 'n', '2011-05-20_0', '0'), ('A20', '30', 'f', 'b', 'n', '2011-05-22_0', '0'), ('A21', '20', 'f', 'a', 'n', '2011-05-20_0', '0'), ('A22', '10', 'f', 'b', 'n', '2011-05-20_0', '0'), ('A23', '20', 'm', 'a', 'n', '2011-05-20_0', '0'), ('A24', '10', 'f', 'b', 'n', '2011-05-20_0', '0'), ('A25', '10', 'm', 'o', 'n', '2011-05-20_0', '0'), ('A26', '10', 'f', 'o', 'n', '2011-05-20_0', '0'), ('A27', '20', 'm', 'a', 'n', '2011-05-20_0', '0'), ('A28', '30', 'm', 'b', 'n', '2011-05-20_0', '0'), ('A29', '10', 'f', 'a', 'n', '2011-05-20_0', '0'), ('A3', '40', 'm', 'ab', 'n', '2011-05-20_0', '0'), ('A30', '20', 'f', 'o', 'n', '2011-05-20_0', '0'), ('A31', '10', 'm', 'ab', 'n', '2011-05-20_0', '0'), ('A32', '30', 'f', 'b', 'n', '2011-05-20_0', '0'), ('A33', '20', 'm', 'a', 'n', '2011-05-20_0', '0'), ('A34', '10', 'f', 'ab', 'n', '2011-05-20_0', '0'), ('A35', '20', 'm', 'a', 'n', '2011-05-20_0', '0'), ('A36', '20', 'f', 'b', 'n', '2011-05-20_0', '0'), ('A37', '30', 'm', 'o', 'n', '2011-05-20_0', '0'), ('A38', '10', 'f', 'o', 'n', '2011-05-20_0', '0'), ('A39', '30', 'f', 'a', 'n', '2011-05-20_0', '0'), ('A4', '30', 'f', 'b', 'n', '2011-05-20_0', '0'), ('A40', '30', 'm', 'a', 'n', '2011-05-20_0', '0'), ('A41', '40', 'f', 'o', 'n', '2011-05-20_0', '0'), ('A42', '40', 'm', 'b', 'n', '2011-05-20_0', '0'), ('A43', '20', 'f', 'ab', 'n', '2011-05-20_0', '0'), ('A44', '10', 'm', 'b', 'n', '2011-05-20_0', '0'), ('A45', '10', 'f', 'ab', 'n', '2011-05-20_0', '0'), ('A46', '20', 'm', 'ab', 'n', '2011-05-20_0', '0'), ('A47', '10', 'f', 'ab', 'n', '2011-05-20_0', '0'), ('A48', '10', 'f', 'b', 'n', '2011-05-20_0', '0'), ('A49', '20', 'm', 'b', 'n', '2011-05-20_0', '0'), ('A5', '20', 'f', 'a', 'n', '2011-05-20_0', '0'), ('A50', '30', 'm', 'o', 'n', '2011-05-20_0', '0'), ('A6', '10', 'm', 'ab', 'n', '2011-05-20_0', '0'), ('A7', '20', 'f', 'o', 'n', '2011-05-20_0', '0'), ('A8', '30', 'f', 'b', 'n', '2011-05-20_0', '0'), ('A9', '40', 'f', 'b', 'n', '2011-05-20_0', '0');
COMMIT;

-- ----------------------------
--  Procedure structure for `out_confirmreq_proc`
-- ----------------------------
DROP PROCEDURE IF EXISTS `out_confirmreq_proc`;
delimiter ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `out_confirmreq_proc`(IN p_select_category varchar(40), IN p_user_udid varchar(40))
    READS SQL DATA
BEGIN		
	IF (p_select_category IS NULL) THEN
		SELECT req.req_id,req.req_content,req.exp_a,req.exp_b,req.exp_c,req.exp_d
		FROM lr_rstconfirm rst JOIN lr_request req ON rst.req_id = req.req_id
		WHERE rst.udid = p_user_udid;
	ELSE
		SELECT req.req_id,req.req_content,req.exp_a,req.exp_b,req.exp_c,req.exp_d
		FROM lr_rstconfirm rst JOIN lr_request req ON rst.req_id = req.req_id
		WHERE (req.req_category = p_select_category AND rst.udid = p_user_udid);
	END IF;
END
 ;;
delimiter ;

-- ----------------------------
--  Procedure structure for `out_req_proc`
-- ----------------------------
DROP PROCEDURE IF EXISTS `out_req_proc`;
delimiter ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `out_req_proc`(p_user_sex char(1), p_select_category varchar(20), p_search_word varchar(65535))
BEGIN
	start transaction;
		IF (p_select_category is null) THEN
			SELECT req.req_id,req.req_content,req.exp_a,req.exp_b,req.exp_c,req.exp_d
			FROM lr_request req JOIN lr_user usr ON req.udid = usr.udid
			WHERE usr.sex <> p_user_sex
			AND req.req_content LIKE CONCAT('%',p_search_word,'%');
		ELSE
			SELECT req.req_id,req.req_content,req.exp_a,req.exp_b,req.exp_c,req.exp_d
			FROM lr_request req JOIN lr_user usr ON req.udid = usr.udid
			WHERE usr.sex <> p_user_sex
			AND req.req_category = p_select_category AND
			req.req_content LIKE CONCAT('%',p_search_word,'%');
		END IF;	
	commit;
END
 ;;
delimiter ;

-- ----------------------------
--  Procedure structure for `random_req_proc`
-- ----------------------------
DROP PROCEDURE IF EXISTS `random_req_proc`;
delimiter ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `random_req_proc`(p_user_sex char(1), OUT p_req_reqid INT(10), OUT p_req_category VARCHAR(20), OUT p_req_content VARCHAR(255), OUT p_req_exp_a VARCHAR(100), OUT p_req_exp_b VARCHAR(100), OUT p_req_exp_c VARCHAR(100), OUT p_req_exp_d VARCHAR(100))
    DETERMINISTIC
BEGIN         
    SELECT req.req_id,req.req_category,req.req_content,req.exp_a,req.exp_b,req.exp_c,req.exp_d
	INTO p_req_reqid,p_req_category,p_req_content,p_req_exp_a,p_req_exp_b,p_req_exp_c,p_req_exp_d
    FROM lr_request req JOIN lr_user usr ON req.udid = usr.udid
    AND usr.sex <> p_user_sex
    AND req.res_count < 1000      
    ORDER BY rand() limit 1;   
END
 ;;
delimiter ;

-- ----------------------------
--  Procedure structure for `result_res_proc`
-- ----------------------------
DROP PROCEDURE IF EXISTS `result_res_proc`;
delimiter ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `result_res_proc`(p_user_udid varchar(40), p_req_reqid int, p_select_bloodtype varchar(2), p_select_age char(2), OUT p_req_expa float, OUT p_req_expb float, OUT p_req_expc float, OUT p_req_expd float)
BEGIN
     -- declare l_count int	
	DECLARE l_user_sex char(1) default null;
	DECLARE l_user_auttype char(1) default null;
	DECLARE l_user_rstcount varchar(20) default null;
	DECLARE l_date DATE default null;
	DECLARE l_count int default 0;
	DECLARE l_chk char(1) default 'n';
	DECLARE l_currDate varchar(10) default null;
	DECLARE l_countStr varchar(10) default null;
	DECLARE l_rstConfirmCnt int default 0;

	SELECT aut_type,rst_count,sex
	INTO l_user_auttype,l_user_rstcount,l_user_sex
	FROM lr_user WHERE udid = p_user_udid;

	-- 일반사용자인 경우
	IF (l_user_auttype = 'n') THEN
		SET l_currDate = SPLIT_STRING(l_user_rstcount,'_',1);
		SET l_date = STR_TO_DATE(l_currDate, 'yyyy-mm-dd');
		SET l_countStr = SPLIT_STRING(l_user_rstcount,'_',2);			
		SET l_count = CAST(l_countStr as signed);
		--  오늘날짜인 경우
		IF (l_date <> CURDATE()) THEN
			-- 오늘날짜가 아닌경우 오늘날짜로 갱신
			SET l_date = CURDATE();			
			SET l_count = 0;
		END IF;
		
		-- 갱신
		IF (l_count = 0) THEN
			SET l_chk = 'y';
			SET l_count = l_count + 1;		
			UPDATE lr_user SET rst_count = CONCAT(CURDATE(),'_',CAST(l_count as char))
			WHERE udid = p_user_udid;		
		END IF;
	-- 프리미어 사용자인 경우
	ELSEIF (l_user_auttype = 'p') THEN
		SET l_chk = 'y';		
		UPDATE lr_user SET rst_count = CONCAT(STR_TO_DATE(CURDATE(), 'yyyy-mm-dd'),'_0') WHERE udid = p_user_udid;
	-- 블럭 사용자인 경우
	ELSEIF (l_user_auttype = 'b') THEN
		SET l_chk = 'n';
	END IF;
	
	IF (l_chk = 'y') THEN
		-- 결과확인 여부를 갱신한다.
		SELECT COUNT(*)
		INTO l_rstConfirmCnt
		FROM lr_rstconfirm WHERE req_id = p_req_reqid AND udid = p_user_udid;
		IF (l_rstConfirmCnt = 0) THEN
			INSERT INTO lr_rstconfirm VALUES (p_req_reqid, p_user_udid, 'y');
		ELSE
			UPDATE lr_rstconfirm SET rst_confirm = 'y' WHERE req_id = p_req_reqid AND udid = p_user_udid;
		END IF;
 
		SET p_req_expa = out_exppercent_func(p_req_reqid, l_user_sex, p_select_bloodtype, p_select_age, "A");
		SET p_req_expb = out_exppercent_func(p_req_reqid, l_user_sex, p_select_bloodtype, p_select_age, "B");
		SET p_req_expc = out_exppercent_func(p_req_reqid, l_user_sex, p_select_bloodtype, p_select_age, "C");
		SET p_req_expd = out_exppercent_func(p_req_reqid, l_user_sex, p_select_bloodtype, p_select_age, "D");
	ELSE
		SET p_req_expa = 0.0;
		SET p_req_expb = 0.0;
		SET p_req_expc = 0.0;
		SET p_req_expd = 0.0;
	END IF;
END
 ;;
delimiter ;

-- ----------------------------
--  Function structure for `out_exppercent_func`
-- ----------------------------
DROP FUNCTION IF EXISTS `out_exppercent_func`;
delimiter ;;
CREATE DEFINER=`root`@`localhost` FUNCTION `out_exppercent_func`(p_req_reqid int, p_user_sex char(1), p_user_bloodtype varchar(2), p_user_age char(2), p_res_response char(1)) RETURNS float
BEGIN
	declare l_exp_select_count INT Default 0;
	declare l_exp_total_count INT Default 0;
	declare l_exp_percent FLOAT Default 0.0;
	
	IF (p_user_bloodtype is null) AND (p_user_age is null) THEN
		SELECT COUNT(*)
        INTO l_exp_select_count
        FROM lr_response res JOIN lr_user usr ON res.udid = usr.udid
        WHERE res.req_id = p_req_reqid
        AND res.response = p_res_response
        AND usr.sex <> p_user_sex;
		SELECT COUNT(*)
        INTO l_exp_total_count
        FROM lr_response res JOIN lr_user usr ON res.udid = usr.udid
        WHERE res.req_id = p_req_reqid
		AND usr.sex <> p_user_sex;		
		SET l_exp_percent = l_exp_select_count / l_exp_total_count * 100;		
	ELSEIF (p_user_bloodtype is not NULL) AND (p_user_age is NULL) THEN
        SELECT count(*)
        INTO l_exp_select_count
        FROM lr_response res JOIN lr_user usr ON res.udid = usr.udid
        WHERE res.req_id = p_req_reqid
        AND res.response = p_res_response
        AND usr.sex <> p_user_sex
        AND usr.bloodtype = p_user_bloodtype;
		SELECT COUNT(*)
        INTO l_exp_total_count
        FROM lr_response res JOIN lr_user usr ON res.udid = usr.udid
        WHERE res.req_id = p_req_reqid
		AND usr.sex <> p_user_sex;		
		SET l_exp_percent = l_exp_select_count / l_exp_total_count * 100;		
    ELSEIF (p_user_bloodtype is NULL) AND (p_user_age is not NULL) THEN
        SELECT count(*)
        INTO l_exp_select_count
        FROM lr_response res JOIN lr_user usr ON res.udid = usr.udid
        WHERE res.req_id = p_req_reqid
        AND res.response = p_res_response
        AND usr.sex <> p_user_sex
        AND usr.age = p_user_age;		
		SELECT COUNT(*)
        INTO l_exp_total_count
        FROM lr_response res JOIN lr_user usr ON res.udid = usr.udid
        WHERE res.req_id = p_req_reqid
		AND usr.sex <> p_user_sex;		
		SET l_exp_percent = l_exp_select_count / l_exp_total_count * 100;		
    ELSE
        SELECT count(*)
        INTO l_exp_select_count
        FROM lr_response res JOIN lr_user usr ON res.udid = usr.udid
        WHERE res.req_id = p_req_reqid
        AND res.response = p_res_response
        AND usr.sex <> p_user_sex
        AND usr.bloodtype = p_user_bloodtype
        AND usr.age = p_user_age;		
		SELECT COUNT(*)
        INTO l_exp_total_count
        FROM lr_response res JOIN lr_user usr ON res.udid = usr.udid
        WHERE res.req_id = p_req_reqid
		AND usr.sex <> p_user_sex;		
		SET l_exp_percent = l_exp_select_count / l_exp_total_count * 100;		
	END IF; 
	SET l_exp_percent = ROUND(l_exp_percent,2);
		
	RETURN l_exp_percent;
END
 ;;
delimiter ;

-- ----------------------------
--  Function structure for `SPLIT_STRING`
-- ----------------------------
DROP FUNCTION IF EXISTS `SPLIT_STRING`;
delimiter ;;
CREATE DEFINER=`root`@`localhost` FUNCTION `SPLIT_STRING`( x text, delim varchar(32), pos int ) RETURNS text CHARSET latin1
RETURN SUBSTRING_INDEX(SUBSTRING_INDEX(x, delim, pos), delim, -1)
 ;;
delimiter ;

SET FOREIGN_KEY_CHECKS = 1;
